/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package writetoexcel;


import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.makumba.LogicException;

/**
 * This class provides methods to convert vector to Microsoft Excel file.
 *
 * @author Sara Tily
 */
public class WriteToExcel {

    /** Converts the given data and writes the results in as an Excel file on the given output stream. */
    WriteToExcel(OutputStream out, Vector<Vector> data, String sheetName) throws LogicException {
        
        try {
            // Creat empty workbook & sheet
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();
            
            if (sheetName != null) {
            	wb.setSheetName(0, sheetName);
            }

            
            short rowIndex = 0;
            HSSFRow row = sheet.createRow(rowIndex);
            HSSFCell headerCell = row.createCell((short) 0);
            

            
            HSSFCellStyle cellStyle = wb.createCellStyle();
            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            headerCell.setCellStyle(cellStyle);

            
            
            for (int i = 0; i < data.size(); i++) {
                row = sheet.createRow(i + rowIndex);
                for (short j = 0; j < data.elementAt(i).size(); j++) {
                    HSSFCell cell = row.createCell(j);
                    Object value = data.elementAt(i).elementAt(j);
                    writeToExcelCell(cell, value, wb);
                }
            }
            
            wb.write(out);
        } catch (IOException e) {
            throw new LogicException("Error writing Excel file: " + e.getMessage());
        }
    }

  
    /** write the given value to an Excel cell. */
    private static void writeToExcelCell(HSSFCell cell, Object value, HSSFWorkbook wb) {
        if (value != null) {
            if (value instanceof Number) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                // we style the cell as a date (and time). It is important to create a new cell style from the workbook
                // otherwise you can end up modifying the built in style and effecting not only this cell but other cells.
                HSSFCellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
                cell.setCellStyle(cellStyle);
                cell.setCellValue((Date) value);
            } else if (value instanceof String) {
                cell.setCellValue(escapeCarriageReturn((String) value)); 
            } else if (value instanceof Boolean) {
                cell.setCellValue(((Boolean) value).booleanValue());
            } else {
                cell.setCellValue(escapeCarriageReturn(String.valueOf(value)));
            }
        }
    }
    
    private static String escapeCarriageReturn(String s) {
        return s.replaceAll("\r", ""); //remove CRs (but leave LFs))
    } 
}
